package wsp.dailymarket.deals;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import javax.annotation.Resource;
import javax.faces.application.FacesMessage;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.SessionScoped;
import javax.faces.context.FacesContext;
import javax.faces.model.ArrayDataModel;
import javax.faces.model.DataModel;
import javax.sql.DataSource;

/**
 * Represents the list of categories stored in the database
 * @author Matt
 */
@ManagedBean(name = "catListBean")
@SessionScoped
public class CategoryListBean {

	private ArrayList<EditableCategory> catList;
	@Resource(name = "jdbc/dmderby")
	private DataSource ds;
	private String newCat_name;
	private String newCat_desc;
	private int newCat_parent;

	public CategoryListBean() {
		catList = new ArrayList<EditableCategory>();
	}

	public DataModel<EditableCategory> getAllCategories() {
		if (catList.isEmpty()) {
			this.reloadCategories();
		}
		return new ArrayDataModel(catList.toArray());
	}

	private void reloadCategories() {
		catList.clear();
		Connection conn = null;
		try {
			try {
				conn = ds.getConnection();
				Statement stmt = conn.createStatement();
				String sql = "SELECT child.*, parent.CATEGORY_NAME AS PARENT_NAME FROM app.categories AS child LEFT OUTER JOIN app.categories AS parent ON child.PARENT = parent.ID";
				ResultSet rs = stmt.executeQuery(sql);
				while (rs.next()) {
					EditableCategory newCat = new EditableCategory();
					newCat.setID(rs.getInt("ID"));
					newCat.setName(rs.getString("CATEGORY_NAME"));
					newCat.setDesc(rs.getString("DESCRIPTION"));
					newCat.setParentID(rs.getInt("PARENT"));
					newCat.setParentName(rs.getString("PARENT_NAME"));
					catList.add(newCat);
				}
			} finally {
				if (conn != null) {
					conn.close();
				}
			}
		} catch (SQLException ex) {
			ex.printStackTrace(System.err);
			FacesMessage msg = new FacesMessage();
			msg.setSeverity(FacesMessage.SEVERITY_FATAL);
			msg.setSummary("Database Error!");
			FacesContext.getCurrentInstance().addMessage(null, msg);
		}
	}

	public String doCommitEditCategory(EditableCategory cat) {
		Connection conn = null;
		try {
			try {
				conn = ds.getConnection();
				Statement stmt = conn.createStatement();
				String sql = "UPDATE APP.CATEGORIES SET "
						+ "category_name = '"
						+ cat.getName()
						+ "', description = '"
						+ cat.getDesc()
						+ "'";
				if (stmt.executeUpdate(sql) == 1) {
					this.reloadCategories();
				} else {
					FacesMessage msg = new FacesMessage();
					msg.setSeverity(FacesMessage.SEVERITY_ERROR);
					msg.setSummary("There is already a category with that name!");
					FacesContext.getCurrentInstance().addMessage(null, msg);
				}
			} finally {
				if (conn != null) {
					conn.close();
				}
			}
		} catch (SQLException ex) {
			ex.printStackTrace(System.err);
			FacesMessage msg = new FacesMessage();
			msg.setSeverity(FacesMessage.SEVERITY_FATAL);
			msg.setSummary("Database Error!");
			FacesContext.getCurrentInstance().addMessage(null, msg);
		}
		return null;
	}

	public String doCancelEditCategory() {
		for (int i = 0; i < catList.size(); i++) {
			this.catList.get(i).setEditing(false);
		}
		return null;
	}

	public String doEditCategory(EditableCategory cat) {
		this.doCancelEditCategory();
		this.catList.get(catList.indexOf(cat)).setEditing(true);
		return null;
	}

	public String doDeleteCategory(EditableCategory cat) {
		Connection conn = null;
		try {
			try {
				conn = ds.getConnection();
				Statement stmt = conn.createStatement();
				String sql = "DELETE FROM APP.CATEGORIES WHERE ID="
						+ cat.getID();
				if (stmt.executeUpdate(sql) == 1) {
					this.reloadCategories();
				} else {
					FacesMessage msg = new FacesMessage();
					msg.setSeverity(FacesMessage.SEVERITY_ERROR);
					msg.setSummary("Unable to delete the category "
							+ cat.getName()
							+ "!");
					FacesContext.getCurrentInstance().addMessage(null, msg);
				}
			} finally {
				if (conn != null) {
					conn.close();
				}
			}
		} catch (SQLException ex) {
			ex.printStackTrace(System.err);
			FacesMessage msg = new FacesMessage();
			msg.setSeverity(FacesMessage.SEVERITY_FATAL);
			msg.setSummary("Database Error!");
			FacesContext.getCurrentInstance().addMessage(null, msg);
		}
		return null;
	}

	public String doSaveNewCategory() {
		// Save New Category
		Connection conn = null;
		try {
			try {
				conn = ds.getConnection();
				Statement stmt = conn.createStatement();
				String sql = "INSERT INTO APP.CATEGORIES (category_name, description, parent) VALUES ('"
						+ this.newCat_name
						+ "', '"
						+ this.newCat_desc
						+ "', "
						+ this.newCat_parent
						+ ")";
				if (stmt.executeUpdate(sql) == 1) {
					this.reloadCategories();
					this.newCat_name = "";
					this.newCat_desc = "";
					FacesMessage msg = new FacesMessage();
					msg.setSeverity(FacesMessage.SEVERITY_INFO);
					msg.setSummary("Category saved!");
					FacesContext.getCurrentInstance().addMessage(null, msg);
					return "manage_categories";
				} else {
					FacesMessage msg = new FacesMessage();
					msg.setSeverity(FacesMessage.SEVERITY_ERROR);
					msg.setSummary("There is already a category with that name!");
					FacesContext.getCurrentInstance().addMessage(null, msg);
					return null;
				}
			} finally {
				if (conn != null) {
					conn.close();
				}
			}
		} catch (SQLException ex) {
			ex.printStackTrace(System.err);
			FacesMessage msg = new FacesMessage();
			msg.setSeverity(FacesMessage.SEVERITY_FATAL);
			msg.setSummary("Database Error!");
			FacesContext.getCurrentInstance().addMessage(null, msg);
			return null;
		}
	}

	public void setNewCatName(String newValue) {
		this.newCat_name = newValue;
	}

	public String getNewCatName() {
		return this.newCat_name;
	}

	public void setNewCatDesc(String newValue) {
		this.newCat_desc = newValue;
	}

	public String getNewCatDesc() {
		return this.newCat_desc;
	}

	public void setNewCatParent(int newValue) {
		this.newCat_parent = newValue;
	}

	public int getNewCatParent() {
		return this.newCat_parent;
	}

	
}
